import sys
from pathlib import Path

file = Path(__file__).resolve()
parent, top = file.parent, file.parents[2]

sys.path.append(str(top))
try:
    sys.path.remove(str(parent))
except ValueError: # Already removed
    pass

__package__ = 'src.model'

from ..utils.ids import create_id, datetime_8, get_ip_address
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy import Column, String, BigInteger, CHAR, DateTime, Integer
from ..utils.database import Base, infer_engine, InferSessionLocal
import uuid


class Failure(Base):
    __tablename__ = 'tb_failure'
    __table_args__ = {'comment': '缺陷表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='id')
    failure_type = Column(String(36), nullable=False, comment='缺陷类型')
    failure_name = Column(String(36), nullable=False, comment='缺陷名称')
    failure_id = Column(BigInteger, nullable=False, comment='缺陷 id 做关联查询')
    del_flag = Column(
        CHAR(1), nullable=False, server_default='0', comment="是否删除, 1-已删除, 0-未删除")


class Lot(Base):
    __tablename__ = 'tb_lot'
    __table_args__ = {'comment': 'lot 表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    lot_code = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()), comment='lot code')
    lot_name = Column(String(36), nullable=False, comment='lot名称')
    server_host = Column(String(16), nullable=False, default=get_ip_address, comment='ai 服务器所在的 ip 地址')
    material_code = Column(LONGTEXT, nullable=False, comment='料号code')
    aoi_code = Column(LONGTEXT, comment='aoi code')
    dir_path = Column(String(255), nullable=False, server_default='', comment='lot文件夹目录路径')
    status = Column(CHAR(1), nullable=False, server_default='0', comment='状态: 0-正在运行, 1-已完成, 2-运行出错')
    comment = Column(String(50), comment='lot 备注')
    start_time = Column(DateTime, default=datetime_8, comment='开始时间')
    end_time = Column(DateTime, comment='结束时间')


# 每个 panel 的图片
class InferPanelImage(Base):
    __tablename__ = 'tb_infer_panel_image'
    __table_args__ = {'comment': 'Panel 图片表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    panel_image_code = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    lot_code = Column(String(36), nullable=False, comment='lot code 做关联')
    panel_code = Column(String(36), nullable=False, comment='pannel image code 做关联')
    image_path = Column(String(255), nullable=False, comment='图片路径')
    image_name = Column(String(255), nullable=False, comment='图片名')
    thumb_file_path = Column(String(255), nullable=False, comment='图片的缩略图路径')
    detection_info = Column(LONGTEXT, comment='检测信息')
    aoi_failures = Column(LONGTEXT, comment='aoi 缺陷')
    pattern_failure = Column(String(32), comment='pattern 缺陷')


# 每个 panel 表
class InferPanel(Base):
    __tablename__ = 'tb_infer_panel'
    __table_args__ = {'comment': 'Panel 表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    panel_code = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    server_host = Column(String(16), nullable=False, default=get_ip_address, comment='ai 服务器所在的 ip 地址')
    panel_id = Column(String(64), nullable=False, comment='Panel ID')
    lot_code = Column(String(255), nullable=False, comment='lot code 做关联')
    aoi_code = Column(String(255), comment='aoi code 做关联')
    material_code = Column(String(255), nullable=False, comment='materia code 做关联')
    ai_result = Column(Integer, comment='AI 检测结果, 0-OK, 1-NG')
    aoi_result = Column(Integer, comment='AOI 检测结果, 0-OK, 1-NG')
    judgment = Column(Integer, default=0, comment='人工复判结果, 0-OK, 1-NG')
    start_time = Column(DateTime, default=datetime_8, comment='aoi 开始时间')
    failure_code = Column(String(36), comment='人工输入的缺陷代码')
    img_roi = Column(String(255), comment='图片的有效区域')


# 每个 panel 对应的图片
class Material(Base):
    __tablename__ = 'tb_material'
    __table_args__ = {'comment': 'material 表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}
    material_code =  Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    server_host = Column(String(16), nullable=False, default=get_ip_address, comment='ai 服务器所在的 ip 地址')
    material_name = Column(String(36), nullable=False, comment='料号')
    background_image = Column(String(255), comment='物料背景图')
    thumb_file_path = Column(String(255), nullable=False, comment='图片的缩略图路径')
    create_time = Column(DateTime, default=datetime_8, comment='物料创建时间')
    update_time = Column(DateTime, default=datetime_8, comment='物料更新时间')
    comment = Column(String(50), comment='物料备注')
    parameter = Column(LONGTEXT, comment='缺陷参数')


# 每个远程拷贝任务的时间点对应的图片
class ScpJob(Base):
    __tablename__ = 'tb_scp_job'
    __table_args__ = {'comment': '远程拷贝任务表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    job_code = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    lot_code = Column(String(36), comment='lot code 做关联')
    material_code = Column(String(36), comment='material code 做关联')
    status = Column(CHAR(1), server_default='1', comment='拷贝任务状态, 0-无数据, 1-复制排队中, 2-复制中, 3-已停止, 4-复制已完成, 5-推理排队中, 6-推理中, 7-出错, 8-推理已完成')
    aoi_code = Column(String(36), nullable=False, comment='远程服务器 aoi')
    start_datetime = Column(DateTime, default=datetime_8, comment='拷贝任务开始时间')
    end_datetime = Column(DateTime, default=datetime_8, comment='拷贝任务结束时间')
    last_flag = Column(CHAR(1), server_default='0', comment="是否是最后一个拷贝任务, 1-是, 0-不是")


class AOI(Base):
    __tablename__ = 'tb_aoi'
    __table_args__ = {'comment': '远程拷贝 aoi 服务器表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}
    aoi_code = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    aoi_name = Column(String(36), nullable=False, comment='aoi 名称')
    server_host = Column(String(16), nullable=False, default=get_ip_address, comment='ai 服务器所在的 ip 地址')
    hostname = Column(String(20), comment='AOI 服务器 ip 地址')
    port = Column(String(10), server_default='22',comment='AOI 服务器 ssh 端口')
    username = Column(String(20), comment='AOI 服务器用户名')
    password = Column(String(20), comment='AOI 服务器密码')
    lotdir = Column(String(255), comment='lot 目录')


if __name__ == "__main__":

    flag = "done"
    new_flag = None

    # # 创建表

    # table_name = ["tb_failure", "tb_failure_image", "tb_scp_job", 'tb_panel_image', 'tb_material', 'tb_aoi']
    # attr_name = "server_host"
    # column_type = "varchar(16)"
    # comment = "ai 服务器所在的 ip 地址"

    # aois = [["AOIQH107", "192.168.110.107", "ctos", "1"], ["AOIQH108", "192.168.110.108", "ctos", "1"]]
    # with InferSessionLocal() as session:
    #     for aoi in aois:
    #         aoi_obj = AOI(aoi_name=aoi[0], hostname=aoi[1], username=aoi[2], password=aoi[3])
    #         session.add(aoi_obj)

    #     session.commit()


    # aois = [["AOIQH083", "192.168.110.83", "ctos", "1"], ["AOIQH087", "192.168.110.87", "ctos", "1"]]
    # with InferSessionLocal() as session:
    #     for aoi in aois:
    #         aoi_obj = AOI(aoi_name=aoi[0], hostname=aoi[1], username=aoi[2], password=aoi[3])
    #         session.add(aoi_obj)

    #     session.commit()

    # aois = [["AOIQH084", "192.168.110.84", "ctos", "1"], ["AOIQH109", "192.168.110.109", "ctos", "1"]]
    # with InferSessionLocal() as session:
    #     for aoi in aois:
    #         aoi_obj = AOI(aoi_name=aoi[0], hostname=aoi[1], username=aoi[2], password=aoi[3])
    #         session.add(aoi_obj)

    #     session.commit()

    # # 新建料号表
    # # 先删除表
    # with InferSessionLocal() as session:
    #     session.execute(text('DROP TABLE %s ' % (table_name)))

    # 创建表
    Base.metadata.create_all(bind=infer_engine)

    # # # 修改表结构
    # with InferSessionLocal() as session:
    #     # # default = "/data/jd/material/G63A/PowerOff.bmp"
    #     # tname = "tb_lot"
    #     # attr_name = "comment"
    #     # column_type = "varchar(50)"
    #     # comment = "lot 备注"

    #     # # 添加列
    #     # session.execute(text('ALTER TABLE %s ADD COLUMN %s %s COMMENT "%s"' % (tname, attr_name, column_type, comment)))

    #     # tname = "tb_panel_image"
    #     # attr_name = "start_time"
    #     # column_type = "datetime"
    #     # comment = "开始时间"
    #     # session.execute(text('ALTER TABLE %s ADD COLUMN %s %s NOT NULL COMMENT "%s"' % (tname, attr_name, column_type, comment)))

    #     # tname = "tb_panel_image"
    #     # attr_name = "end_time"
    #     # column_type = "datetime"
    #     # comment = "结束时间"
    #     # session.execute(text('ALTER TABLE %s ADD COLUMN %s %s NOT NULL COMMENT "%s"' % (tname, attr_name, column_type, comment)))


    #     tname = "tb_scp_job"
    #     attr_name = "del_flag"
    #     column_type = "char(1)"
    #     comment = "逻辑删除, 1-删除, 0-不删除"
    #     session.execute(text('ALTER TABLE %s ADD COLUMN %s %s NOT NULL COMMENT "%s"' % (tname, attr_name, column_type, comment)))

    #     session.commit()
      
        # session.execute(text('ALTER TABLE %s MODIFY %s %s NOT NULL DEFAULT "%s" COMMENT "%s"' % (table_name, attr_name, column_type, default, comment)))

    #     if not flag:

    #         # 添数据到 tb_material
    #         material_name = 'G163A'
    #         background_image = "/data/jd/material/G163A/PowerOff.bmp"
        
    #         material = Material(material_name=material_name, background_image=background_image, parameter=json.dumps(all_parameter))
    #         session.add(material)
    #         session.commit()

    #     if not flag:

    #         # 更新 tb_failure 表
    #         table_name = "tb_failure"

    #         # 逻辑删除
    #         del_failure = ["OK", "P304"]
    #         new_failure = ["P4FAKE", "P5FAKE"]

    #         for failure in del_failure:
    #             failure_obj = session.query(Failure).filter_by(failure_name=failure).first()
    #             failure_obj.del_flag = '1'

    #         current_failure_id = session.query(Failure).order_by(Failure.id.desc()).limit(1).first().failure_id
    #         for failure in new_failure:
    #             current_failure_id += 1
    #             failure_obj = Failure(failure_type=failure, failure_name=failure, failure_id=current_failure_id)
    #             session.add(failure_obj)

    #         session.commit()

    #     if not new_flag:
    #         table_name = "tb_panel_image"
    #         attr_name = "ai_result"
    #         column_type = "int"
    #         comment = "AI 检测结果, 1-OK, 2-NG, 3-Gray"
    #         session.execute(text('ALTER TABLE %s MODIFY %s %s COMMENT "%s"' % (table_name, attr_name, column_type, comment)))

    #         session.commit()

    # # 添加新的 failure id
    # table_name = "tb_failure"

    # # 逻辑删除
    # failure = ["PH01", "大板元不良"]

    # with InferSessionLocal() as session:
    #     failure_obj = Failure(id=15, failure_type=failure[1], failure_name=failure[0], failure_id=14)
    #     session.add(failure_obj)

        # session.commit()
    # with InferSessionLocal() as session:
    #     aoi1 = AOI(aoi_name="aoi1", hostname="192.168.2.13", username="HUAWEI", password="123456", lotdir="D:\jdipic", logdir="D:\jdilog")
    #     session.add(aoi1)

    #     aoi2 = AOI(aoi_name="aoi2", hostname="192.168.3.2", username="Administrator", password="123456", lotdir="D:\jdipic", logdir="D:\jdilog")
    #     session.add(aoi2)

    #     session.commit()
